﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

namespace Nova_DAL
{
    public class TBase_Position
    {
        DbHelper dbHelper = new DbHelper();
        string strSQL = "";
        DataSet ds = null;
        DataSet dss = null;
        DbCommand cmd = null;
        DbDataReader dbdr = null;
        string lblNo_Line = "";

        ///<summary>
        ///获取货位信息
        ///</summary>
        ///<param name="lbl_LaneNo">巷道号</param>
        ///<returns>返回DataTable</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-7-1]
        ///</summary>
        public DataTable DTableTBase_Position(string lbl_LaneNo)
        {
            DataTable myTable = new DataTable("mytable");
            lblNo_Line = " [ " + lbl_LaneNo.ToString() + " ] ";

            try
            {
                myTable.Columns.Add(lblNo_Line, System.Type.GetType("System.String"));

                myTable.Columns.Add("巷道", System.Type.GetType("System.String"));
                //myTable.Columns.Add("1", System.Type.GetType("System.String"));
                strSQL = "Select No_Col from  TBase_Position where No_Lane=" + Convert.ToSByte(lbl_LaneNo) + " group by No_Col order by No_Col";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbdr = dbHelper.ExecuteReader(cmd);
                while (dbdr.Read())
                {
                    myTable.Columns.Add(dbdr["No_Col"].ToString(), System.Type.GetType("System.String"));
                }
                dbdr.Close();
                dbdr.Dispose();


                strSQL = "select No_Line ,No_Layer from TBase_Position where No_Lane=" + Convert.ToSByte(lbl_LaneNo) + " group by No_Line,No_Layer order by No_Line ,No_Layer";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                ds = dbHelper.ExecuteDataSet(cmd);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = myTable.NewRow();
                        dr[lblNo_Line] = ds.Tables[0].Rows[i]["No_Line"].ToString() + "排";

                        dr["巷道"] = ds.Tables[0].Rows[i]["No_Layer"].ToString() + "层";
                        //dr["1"] = " ?  ";

                        strSQL = "Select No_Col,No_Line ,No_Layer,Pos_State as Pos_State from TBase_Position where NO_Line='" + ds.Tables[0].Rows[i]["No_Line"].ToString() + "' and No_Layer = '" + ds.Tables[0].Rows[i]["No_Layer"].ToString() + "' and No_Lane=" + lbl_LaneNo + " order by No_Col";

                        cmd = dbHelper.GetSqlStringCommond(strSQL);
                        dss = dbHelper.ExecuteDataSet(cmd);
                        if (dss.Tables[0].Rows.Count > 0)
                        {
                            for (int j = 0; j < dss.Tables[0].Rows.Count; j++)
                            {
                                if (dss.Tables[0].Rows[j]["Pos_State"] == null)
                                {
                                    //dr[dss.Tables[0].Rows[j]["No_Col"].ToString()] = "0";
                                }
                                else
                                {
                                    dr[dss.Tables[0].Rows[j]["No_Col"].ToString()] = dss.Tables[0].Rows[j]["Pos_State"].ToString();
                                }
                            }
                        }
                        myTable.Rows.Add(dr);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return myTable;
        }

        ///<summary>
        ///获取PID
        ///</summary>
        ///<param name="PID">自增长字段</param>
        ///<returns>返回PID</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-7-2]
        ///</summary>

        public string GetiPID(Nova_MOD.TBase_Position TBase_Position)
        {
            string PID = "";
            try
            {
                strSQL = "select PID from TBase_Position where No_Lane=@No_Lane and No_Line=@No_Line and No_Col=@No_Col and No_Layer=@No_Layer";

                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@No_Lane", DbType.Int32, TBase_Position.No_Lane);
                dbHelper.AddInParameter(cmd, "@No_Line", DbType.Int32, TBase_Position.No_Line);
                dbHelper.AddInParameter(cmd, "@No_Col", DbType.Int32, TBase_Position.No_Col);
                dbHelper.AddInParameter(cmd, "@No_Layer", DbType.Int32, TBase_Position.No_Layer);
                //PID = (int)dbHelper.ExecuteScalar(cmd);
                ds = dbHelper.ExecuteDataSet(cmd);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    PID = ds.Tables[0].Rows[0]["PID"].ToString();
                }

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return PID;
        }


        /// <summary>
        /// 修改货位信息
        /// </summary>
        /// <param name="BtnPermit"></param>
        /// <returns>返回i</returns>
        /// Writer：[夏鑫]
        ///Create Date： [2010-7-1]
        public string UpdatePosition(string iPID, int iPos_State)
        {
            string strErrMsg = "";
            try
            {
                strSQL = @"execute [SP_UpdatePosState] @iPID,@iPos_State,@Res output,@ErrMsg output";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@iPID", DbType.String, iPID);
                dbHelper.AddInParameter(cmd, "@iPos_State", DbType.Int32, iPos_State);
                dbHelper.AddOutParameter(cmd, "@Res", DbType.String, 10);
                dbHelper.AddOutParameter(cmd, "@ErrMsg", DbType.String, 1000);
                dbHelper.ExecuteNonQuery(cmd);
                //strErrMsg = dbHelper.GetParameter(cmd, "@Res").Value.ToString();
                strErrMsg = dbHelper.GetParameter(cmd, "@ErrMsg").Value.ToString();


                //                strSQL = @"declare   @Res   varchar (100)
                //                            declare   @ErrMsg   varchar (100)
                //                            execute   [SP_UpdatePosState]   @iPID,@iPos_State,@Res output ,@ErrMsg output
                //                            select   @Res, @ErrMsg ";
                //                cmd = dbHelper.GetSqlStringCommond(strSQL);
                //                dbHelper.AddInParameter(cmd, "@iPID", DbType.Int32, iPID);
                //                dbHelper.AddInParameter(cmd, "@iPos_State", DbType.Int32, iPos_State);
                //                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return strErrMsg;
        }

        /// <summary>
        /// 修改货位表GridView的宽度和高度
        /// </summary>
        /// <param name="">width,height</param>
        /// <returns>返回1成功，0失败</returns>
        /// Writer：[王成友]
        ///Create Date： [2010-7-2]
        public int iBackGVStyle(string strWidth, string strHeight, string strTableName, string strUserNo)
        {
            int iFlag = 0;
            try
            {
                strSQL = "select PID from TGVStyle where TableName=@strTableName";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@strTableName", DbType.String, strTableName);
                ds = dbHelper.ExecuteDataSet(cmd);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    strSQL = "update TGVStyle set Width=@strWidth, Height=@strHeight where TableName=@strTableName and vUserNo=@strUserNo";
                }
                else
                {
                    strSQL = "insert into TGVStyle(Width,Height,TableName,vUserNo) values(@strWidth,@strHeight,@strTableName,@strUserNo)";
                }
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@strWidth", DbType.String, strWidth);
                dbHelper.AddInParameter(cmd, "@strHeight", DbType.String, strHeight);
                dbHelper.AddInParameter(cmd, "@strTableName", DbType.String, strTableName);
                dbHelper.AddInParameter(cmd, "@strUserNo", DbType.String, strUserNo);
                iFlag = dbHelper.ExecuteNonQuery(cmd);

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return iFlag;
        }

        /// <summary>
        /// 返回货位表GridView的宽度和高度
        /// </summary>
        /// <param name="">width,height</param>
        /// <returns>返回宽，高</returns>
        /// Writer：[王成友]
        ///Create Date： [2010-7-2]
        public DataSet getGVStyle(string strTableName, string strUserNo)
        {
            try
            {
                strSQL = "select * from TGVStyle where TableName =@strTableName and vUserNo=@strUserNo";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@strTableName", DbType.String, strTableName);
                dbHelper.AddInParameter(cmd, "@strUserNo", DbType.String, strUserNo);
                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return ds;
        }

        /// <summary>
        /// 返回货位表中的物品存储分类信息
        /// </summary>
        /// <param name="">巷道iLane</param>
        /// <returns>物品存储分类信息</returns>
        /// Writer：[王成友]
        ///Create Date： [2010-7-8]
        public DataSet DataListTypeOfStorage(int iLaneID)
        {
            try
            {
                cmd = dbHelper.GetStoredProcCommond("Sp_TypeOfStoragePosition");
                dbHelper.AddInParameter(cmd, "@LaneID", DbType.String, iLaneID);
                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return ds;
        }

        /// <summary>
        /// 返回货位表中品质状态
        /// </summary>
        /// <param name="TBase_Position"></param>
        /// <returns></returns>
        /// Writer：[夏鑫]
        ///Create Date： [2010-7-09]
        public string getTypeOfState(Nova_MOD.TBase_Position TBase_Position, string ColField)
        {
            string strTypeOfState = "";
            try
            {
                strSQL = "select " + ColField + " from TBase_Position where No_Lane=@No_Lane and No_Line=@No_Line and No_Col=@No_Col and No_Layer=@No_Layer";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@No_Lane", DbType.String, TBase_Position.No_Lane);
                dbHelper.AddInParameter(cmd, "@No_Line", DbType.String, TBase_Position.No_Line);
                dbHelper.AddInParameter(cmd, "@No_Col", DbType.String, TBase_Position.No_Col);
                dbHelper.AddInParameter(cmd, "@No_Layer", DbType.String, TBase_Position.No_Layer);
                ds = dbHelper.ExecuteDataSet(cmd);
                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0][0].ToString()))
                {
                    strTypeOfState = ds.Tables[0].Rows[0][0].ToString();
                }
                else
                {
                    strTypeOfState = "0";
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return strTypeOfState;
        }

        /// <summary>
        /// 修改货位基本信息表中货位状态
        /// </summary>
        /// <param name="TBase_Position"></param>
        /// <param name="ColField">字段名称</param>
        /// <returns></returns>
        /// Writer：[夏鑫]
        ///Create Date： [2010-7-09]
        public int UpdateTBase_Position(Nova_MOD.TBase_Position TBase_Position, string ColField, string strState)
        {
            int iFlag = 0;
            try
            {
                strSQL = "Update TBase_Position set " + ColField + " =@FieldValue where No_Lane=@No_Lane and No_Line=@No_Line and No_Col=@No_Col and No_Layer=@No_Layer and Pos_State <>8";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@FieldValue", DbType.String, strState);
                dbHelper.AddInParameter(cmd, "@No_Lane", DbType.String, TBase_Position.No_Lane);
                dbHelper.AddInParameter(cmd, "@No_Line", DbType.String, TBase_Position.No_Line);
                dbHelper.AddInParameter(cmd, "@No_Col", DbType.String, TBase_Position.No_Col);
                dbHelper.AddInParameter(cmd, "@No_Layer", DbType.String, TBase_Position.No_Layer);
                iFlag = dbHelper.ExecuteNonQuery(cmd);

            }

            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return iFlag;
        }



        /// <summary>
        /// 获取货位的PID 增加了库区条件
        /// </summary>
        /// <param name="TBase_Position"></param>
        /// <param name="lbl_ZoneID"></param>
        /// <returns></returns>
        public string GetiPID(Nova_MOD.TBase_Position TBase_Position, string lbl_ZoneID)
        {
            string PID = "";
            try
            {
                strSQL = "select PID from TBase_Position where No_Lane=@No_Lane and No_Line=@No_Line and No_Col=@No_Col and No_Layer=@No_Layer and Zone_ID=@Zone_ID";

                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@Zone_ID", DbType.Int32, Convert.ToInt32(lbl_ZoneID));
                dbHelper.AddInParameter(cmd, "@No_Lane", DbType.Int32, TBase_Position.No_Lane);
                dbHelper.AddInParameter(cmd, "@No_Line", DbType.Int32, TBase_Position.No_Line);
                dbHelper.AddInParameter(cmd, "@No_Col", DbType.Int32, TBase_Position.No_Col);
                dbHelper.AddInParameter(cmd, "@No_Layer", DbType.Int32, TBase_Position.No_Layer);
                //PID = (int)dbHelper.ExecuteScalar(cmd);
                ds = dbHelper.ExecuteDataSet(cmd);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    PID = ds.Tables[0].Rows[0]["PID"].ToString();
                }

            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return PID;
        }

        /// <summary>
        /// 修改货位信息 伟星项目
        /// </summary>
        /// <param name="iPID"></param>
        /// <param name="iPos_State"></param>
        /// <returns></returns>
        public string UpdatePositionForWX(string iPID, int iPos_State)
        {
            string strErrMsg = "";
            try
            {
                strSQL = @"execute [SP_UpdatePosState_wx] @iPID,@iPos_State,@Res output,@ErrMsg output";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@iPID", DbType.String, iPID);
                dbHelper.AddInParameter(cmd, "@iPos_State", DbType.Int32, iPos_State);
                dbHelper.AddOutParameter(cmd, "@Res", DbType.String, 10);
                dbHelper.AddOutParameter(cmd, "@ErrMsg", DbType.String, 1000);
                dbHelper.ExecuteNonQuery(cmd);
                //strErrMsg = dbHelper.GetParameter(cmd, "@Res").Value.ToString();
                strErrMsg = dbHelper.GetParameter(cmd, "@ErrMsg").Value.ToString();


                //                strSQL = @"declare   @Res   varchar (100)
                //                            declare   @ErrMsg   varchar (100)
                //                            execute   [SP_UpdatePosState]   @iPID,@iPos_State,@Res output ,@ErrMsg output
                //                            select   @Res, @ErrMsg ";
                //                cmd = dbHelper.GetSqlStringCommond(strSQL);
                //                dbHelper.AddInParameter(cmd, "@iPID", DbType.Int32, iPID);
                //                dbHelper.AddInParameter(cmd, "@iPos_State", DbType.Int32, iPos_State);
                //                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return strErrMsg;
        }

        /// <summary>
        /// 根据库区号、巷道号获取货位状态图案视图表
        /// xgw add for  解决视图加载卡
        /// </summary>
        /// <param name="lbl_ZoneID"></param>
        /// <param name="lbl_LaneNo"></param>
        /// <returns></returns>
        public DataTable DTableTBase_PositionZoneIDPosImg(string lbl_ZoneID, string lbl_LaneNo,int subArea)
        {
            DataTable myTable = new DataTable("mytable");
            lblNo_Line = " [ " + lbl_LaneNo.ToString() + " ] ";

            try
            {
                myTable.Columns.Add(lblNo_Line, System.Type.GetType("System.String"));

                myTable.Columns.Add("巷道", System.Type.GetType("System.String"));
                //myTable.Columns.Add("1", System.Type.GetType("System.String"));
                strSQL = "Select No_Col from  TBase_Position where subArea ="+subArea+" and Zone_ID=" + Convert.ToSByte(lbl_ZoneID) + " and  No_Lane=" + Convert.ToSByte(lbl_LaneNo) + " group by No_Col order by No_Col";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbdr = dbHelper.ExecuteReader(cmd);
                while (dbdr.Read())
                {
                    myTable.Columns.Add(dbdr["No_Col"].ToString(), System.Type.GetType("System.String"));
                }
                dbdr.Close();
                dbdr.Dispose();


                strSQL = "select No_Line ,No_Layer from TBase_Position where subArea =" + subArea + " and Zone_ID=" + Convert.ToSByte(lbl_ZoneID) + " and No_Lane=" + Convert.ToSByte(lbl_LaneNo) + " group by No_Line,No_Layer order by No_Line ,No_Layer";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                ds = dbHelper.ExecuteDataSet(cmd);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        DataRow dr = myTable.NewRow();
                        dr[lblNo_Line] = ds.Tables[0].Rows[i]["No_Line"].ToString() + "排";

                        dr["巷道"] = ds.Tables[0].Rows[i]["No_Layer"].ToString() + "层";
                        //dr["1"] = " ?  ";

                        strSQL = "Select No_Col,No_Line ,No_Layer,Pos_State ,PosImg from TBase_Position  TB left join TPosState TP on TP.PosID=TB.Pos_State  where subArea ="+subArea+" and NO_Line='" + ds.Tables[0].Rows[i]["No_Line"].ToString() + "' and No_Layer = '" + ds.Tables[0].Rows[i]["No_Layer"].ToString() + "' and No_Lane=" + lbl_LaneNo + " and Zone_ID=" + Convert.ToSByte(lbl_ZoneID) + "  order by No_Col";

                        cmd = dbHelper.GetSqlStringCommond(strSQL);
                        dss = dbHelper.ExecuteDataSet(cmd);
                        if (dss.Tables[0].Rows.Count > 0)
                        {
                            for (int j = 0; j < dss.Tables[0].Rows.Count; j++)
                            {
                                if (dss.Tables[0].Rows[j]["Pos_State"] == null)
                                {
                                    //dr[dss.Tables[0].Rows[j]["No_Col"].ToString()] = "0";
                                }
                                else
                                {
                                    dr[dss.Tables[0].Rows[j]["No_Col"].ToString()] = dss.Tables[0].Rows[j]["PosImg"].ToString();
                                    //dr[dss.Tables[0].Rows[j]["No_Col"].ToString()] = dss.Tables[0].Rows[j]["Pos_State"].ToString();
                                }
                            }
                        }
                        myTable.Rows.Add(dr);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return myTable;
        }


        /// <summary>
        /// 人工后备操作 托盘放入立库目标货位
        /// </summary>
        /// <param name="Tray_ID"></param>
        /// <param name="Pos_ID"></param>
        /// <param name="UserName"></param>
        /// <param name="strMessage"></param>
        /// <returns>-1:失败 1：成功</returns>
        public int TrayPosAdd_ManBackUp(string Tray_ID, long Pos_ID, string UserName, out string strMessage)
        {
            int iResNo;
            strMessage = "";
            try
            {
                cmd = dbHelper.GetStoredProcCommond("SP_TrayPosAdd_ManBackUp");
                dbHelper.AddInParameter(cmd, "@Tray_ID", DbType.String, @Tray_ID);
                dbHelper.AddInParameter(cmd, "@Pos_ID", DbType.Int64, Pos_ID);
                dbHelper.AddInParameter(cmd, "@UserName", DbType.String, UserName);
                dbHelper.AddOutParameter(cmd, "@ResNO", DbType.Int32, 100);
                dbHelper.AddOutParameter(cmd, "@ResMsg", DbType.String, 255);
                dbHelper.ExecuteNonQuery(cmd);
                iResNo = Convert.ToInt32(dbHelper.GetParameter(cmd, "@ResNO").Value.ToString());
                strMessage = dbHelper.GetParameter(cmd, "@ResMsg").Value.ToString();
            }
            catch (Exception ex)
            {
                strMessage = "执行存储过程异常跳出";
                iResNo = -1;
            }
            return iResNo;
        }



        /// <summary>
        /// 获取剩余的货位数
        /// </summary>
        /// <returns></returns>
        public int getResidueStorage()
        {
            int rStorage = 0;
            try
            {
                strSQL = @"SELECT COUNT(POS_ID) FROM TBase_Position  WHERE Zone_ID='10' and Pos_State='0'";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                rStorage = Convert.ToInt32(dbHelper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                return -1;
            }

            return rStorage;
        }

        /// <summary>
        /// 获取已经使用的货位数
        /// </summary>
        /// <returns></returns>
        public int getUsedStorage()
        {
            int rStorage = 0;
            try
            {
                strSQL = @"SELECT COUNT(POS_ID) FROM TBase_Position  WHERE Zone_ID='10' and Pos_State<>'0'";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                rStorage = Convert.ToInt32(dbHelper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                return -1;
            }

            return rStorage;
        }

        public bool UpdateByPosId(long posId, int posState)
        {
            string sqlStr = @"UPDATE dbo.TBase_Position SET Pos_State = @Pos_State WHERE Pos_ID=@Pos_ID";
            cmd = dbHelper.GetSqlStringCommond(sqlStr);
            dbHelper.AddInParameter(cmd, "@Pos_State", DbType.Int16, posState);
            dbHelper.AddInParameter(cmd, "@Pos_ID", DbType.Int64, posId);
            int n = dbHelper.ExecuteNonQuery(cmd);
            return n > 0;
        }

        public DataTable GetPosPrimitiveTypeList(int abc_Kind, int typeOfSize)
        {
            string sqlStr = @"SELECT (No_Layer%2) PosPrimitType,COUNT(Pos_ID) PosCount FROM dbo.TBase_Position
                    WHERE ABC_Kind = @abc_Kind  AND Pos_State =0 AND TypeOfSize >= @typeOfSize  GROUP BY (No_Layer%2)";
            cmd = dbHelper.GetSqlStringCommond(sqlStr);
            dbHelper.AddInParameter(cmd, "@abc_Kind", DbType.Int32, abc_Kind);
            dbHelper.AddInParameter(cmd, "@typeOfSize", DbType.Int32, typeOfSize);
            return dbHelper.ExecuteDataTable(cmd);
        }
    }
}
